﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using LibHuman;
using ConvertFont;
using Human.Properties;

namespace Human
{
    public partial class frmCvFox : Form
    {
        string thumuc = "";
        MyClass c_v = new MyClass();
        LibHuman.AccessData mDB = new AccessData();
        Language lan = new Language();
        public frmCvFox()
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
        }

        private void frmCvFox_Load(object sender, EventArgs e)
        {
            //txtPath.Text = Settings.Default.data_fox.ToString();
        }
        string ConStr_human_fox
        {
            get
            {
                return "Provider=vfpoledb.1;Data Source=" + thumuc +";Collating Sequence=general";
            }
        }
        private DataSet get_data_fox(string sql)
        {
            DataSet ds = new DataSet();
            OleDbConnection con;
            OleDbCommand cmd;
            OleDbDataAdapter dest;
            try
            {
                con = new OleDbConnection(ConStr_human_fox);
                con.Open();
                cmd = new OleDbCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                dest = new OleDbDataAdapter(cmd);
                dest.Fill(ds);
                cmd.Dispose();
                con.Dispose();
            }
            catch { }
            return ds;
        }
        private void dm_bp()
        {
            
            DataSet ds = new DataSet();
            ds = get_data_fox("select * from bophan where delete()=.f.");
            int rec = 0;
            rec = ds.Tables[0].Rows.Count;
            mDB.Execute_data("Delete from nhansu.bophan");
            int i=0;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Insert_DMBOPHAN(r["madv"].ToString(), c_v.abc_uni(r["tenbp"].ToString()));
                i++;
                this.Text = "Chuyển bộ phận: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            this.Text = "Chuyển đã xong ";
            this.Update();
        }

        private void cmdOK_Click(object sender, EventArgs e)
        {
            if (txtPath.Text == "")
            {
                MessageBox.Show("Yêu cầu chọn thư mục chứa data Foxpro !",AccessData.Msg,MessageBoxButtons.OK,MessageBoxIcon.Information);
                txtPath.Focus();
                return;
            }
            else
                thumuc = txtPath.Text;
            if (chkBP.Checked) dm_bp();
            if (chkChucvu.Checked) dm_cvu();
            if (chkHV.Checked) dm_hocvan();
            if (chkCtdt.Checked)
                Ctdaotao();
            if (chkBancap.Checked)
                Bancap();
            if (chkNgach.Checked) dm_ngach();
            if (chkLlnv.Checked) Lylichnv();
            if (chkHD.Checked)
                capnhat_HD();
        }
        private bool capnhat_HD()
        {
            string n_hdldso = "", sql = "", n_loaihd = "", n_ngaydenct = "", n_mangach = "", n_tungay = "", n_denngay = "", n_s_begin = "", n_ngayhd = "", n_ngaybhxh = "", n_ngaynghi = "", n_lydonghi = "", n_matd = "", n_ngayvk = ""
                , n_magc = "", n_heso = "", n_chinhthuc_lv = "", n_nam = "", n_ngay_phep = "", n_tvtungay = "", n_tvdenngay = "", s_mangach = "",n_ngaygiulhd="";
            int n_tbnghihuu = 0, n_bangkhen = 0, n_phepthem = 0, n_ko_hoanthanh = 0;
            int n_bhxh = 0, n_bhyt = 0,n_bhtn=0, n_thangnangluong = 0, n_thaisan = 0, n_thoigian = 0, n_bac = 0, n_lan = 0, n_sonphep = 0, i = 0;
            decimal s_hs_lc = 0, n_hs_lc = 0, n_hs_cv = 0, n_hs_tn = 0, n_hs_kv = 0, n_congtacphi = 0,n_sotienhd=0,n_thuongkhac=0,n_hs_dt = 0,ud = 0, n_hs_th = 0, n_hs_ld = 0, n_hs_db = 0, n_hs_dh = 0, n_luonghd = 0, n_luongct = 0, n_hs_vk = 0;
            bool gttv = false;
            string ghichuct = "";
            sql = "select iif(ngaydenct={},'','1') as n_ngaydenct,";
            sql += " iif(tungay={},'','1') as n_tungay,iif(denngay={},'','1') as n_denngay, ";
            sql += " iif(s_begin={},'','1') as n_s_begin,iif(ngayhd={},'','1') as n_ngayhd,";
            sql += " iif(ngaybhxh={},'','1') as n_ngaybhxh,'' as n_ngayvk,";
            sql += " iif(ngaynghi={},'','1') as n_ngaynghi, a.*";
            sql += " from hopdong a where !delete() and !empt(madv) and !empt(loaihd) order by manv";
            DataSet ds = new DataSet();
            ds = get_data_fox(sql);
            dataGridView1.DataSource = ds.Tables[0]; 
            //rec = ds.Tables[0].Rows.Count;
            try
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    n_magc = "";
                    //MessageBox.Show(r["manv"].ToString());
                    n_ngaydenct = (r["n_ngaydenct"].ToString().Trim() != "") ? mDB.StringToMMDDYYYY(r["ngaydenct"].ToString()) : "";
                    n_heso = "";// (r["heso"].ToString() != "") ? r["heso"].ToString().Trim().Substring(r["heso"].ToString().Trim().Length) : "0";
                    n_hdldso = r["hdldso"].ToString();
                    n_matd = "";
                    n_loaihd = r["loaihd"].ToString();
                    n_thoigian = (r["thoigian"].ToString() == "0") ? 0 : int.Parse(r["thoigian"].ToString());
                    n_mangach = r["mangach"].ToString();
                    ghichuct = r["ghichuct"].ToString();
                    n_tungay = (r["n_tungay"].ToString() != "1") ? mDB.StringToMMDDYYYY(r["tungay"].ToString()) : "";
                   // MessageBox.Show(r["n_denngay"].ToString());
                    n_denngay = (r["n_denngay"].ToString().Trim() == "") ? "" : mDB.StringToMMDDYYYY(r["denngay"].ToString());
                    n_s_begin = (r["n_s_begin"].ToString() != "1") ? mDB.StringToMMDDYYYY(r["s_begin"].ToString()) : "";
                    n_ngayhd = (r["n_ngayhd"].ToString().Trim() != "") ? mDB.StringToMMDDYYYY(r["ngayhd"].ToString()) : ""; 
                    n_luonghd = (r["luonghd"].ToString() == "") ? 0 : decimal.Parse(r["luonghd"].ToString());
                    n_ngaybhxh = (r["n_ngaybhxh"].ToString().Trim() != "") ? mDB.StringToMMDDYYYY(r["ngaybhxh"].ToString()) : "";
                    n_ngayvk = ""; 
                    
                    n_sonphep = (r["sonphep"].ToString() != "") ? (int)(decimal.Parse(r["sonphep"].ToString())) : 0;
                    n_bhxh = ((bool)(r["bhxh"])) ? 1 : 0;
                    n_bhyt = ((bool)(r["bhyt"])) ? 1 : 0;
                    n_bhtn = ((bool)(r["bhtn"])) ? 1 : 0;
                    n_hs_lc = (r["hs_lc"].ToString() == "") ? 0 : decimal.Parse(r["hs_lc"].ToString());
                    n_luongct = (r["luongct"].ToString() == "") ? n_luonghd * n_hs_lc : decimal.Parse(r["luongct"].ToString());

                    n_hs_cv = (r["hs_cv"].ToString() == "") ? 0 : decimal.Parse(r["hs_cv"].ToString());
                    n_hs_tn = (r["hs_tn"].ToString() == "") ? 0 : decimal.Parse(r["hs_tn"].ToString());
                    n_hs_kv = (r["hs_kv"].ToString() == "") ? 0 : decimal.Parse(r["hs_kv"].ToString());
                    n_congtacphi = (r["congtacphi"].ToString() == "") ? 0 : decimal.Parse(r["congtacphi"].ToString());
                    n_sotienhd = (r["sotien_hd"].ToString() == "") ? 0 : decimal.Parse(r["sotien_hd"].ToString());
                    n_thuongkhac = (r["thuongkhac"].ToString() == "") ? 0 : decimal.Parse(r["thuongkhac"].ToString());
                    n_hs_dt = (r["hs_dt"].ToString() == "") ? 0 : decimal.Parse(r["hs_dt"].ToString());
                    ud = 0;
                    n_hs_th = 0;
                    n_hs_ld = (r["hs_ld"].ToString() == "") ? 0 : decimal.Parse(r["hs_ld"].ToString());
                    n_hs_db = (r["hs_db"].ToString() == "") ? 0 : decimal.Parse(r["hs_db"].ToString());
                    n_hs_dh = (r["hs_dh"].ToString() == "") ? 0 : decimal.Parse(r["hs_dh"].ToString());
                    n_hs_vk = 0;
                    n_ngaynghi = (r["n_ngaynghi"].ToString().Trim() != "") ? mDB.StringToMMDDYYYY(r["ngaynghi"].ToString()) : "";
                    n_lydonghi = r["heso"].ToString().Substring(5).PadLeft(2,'0');
                    n_bac = 1;
                    DataSet dsdm = new DataSet();                       
                    string s_madv1, s_macv1;

                    dsdm = mDB.get_data("SELECT * FROM " + mDB.user + ".hopdong");
                    s_madv1 = r["madv"].ToString();
                    s_macv1 = r["macv"].ToString();

                    if (n_loaihd == "BC") mDB.upd_tangbc(r["manv"].ToString(), s_madv1, s_macv1, n_loaihd);
                    s_mangach = r["mangach"].ToString();// == -1) ? "" : cboNCC_HD.SelectedValue.ToString();
                    s_hs_lc = (r["hs_lc"].ToString() != "") ? decimal.Parse(r["hs_lc"].ToString()) : 0;
                    n_lan = 1;
                    n_ngay_phep = n_s_begin;
                    n_tbnghihuu = 0;
                    n_bangkhen = 0;
                    n_ko_hoanthanh = 0;
                    n_phepthem = 0;
                    n_thangnangluong = 0;
                    n_nam = "";
                    n_thaisan = 0;                   

                    
                    gttv = mDB.upd_hopdong(r["manv"].ToString(), r["madv"].ToString(), r["macv"].ToString(), n_hdldso, n_ngayhd, n_loaihd, n_tungay, n_denngay, n_thoigian, n_luonghd, n_ngaydenct,
                        n_ngaynghi, "", "", n_sonphep, n_ngaybhxh, n_s_begin, n_bhyt, n_bhxh,n_bhtn, n_hs_cv, n_hs_tn, n_hs_dh, n_hs_ld, n_hs_db, n_hs_kv,
                        n_hs_lc, n_hs_th, n_luongct, n_mangach, n_lydonghi, n_hs_dt,0, ud, n_ngayvk, n_hs_vk, n_matd, n_magc, "", "", n_bac, n_tbnghihuu,
                        n_bangkhen, n_thangnangluong, n_phepthem, "", "", n_chinhthuc_lv, 0, 0, n_ko_hoanthanh, n_nam, n_thaisan,
                        "", "", n_ngay_phep, n_tvtungay, n_tvdenngay, n_lan, n_heso, n_congtacphi,n_sotienhd,n_ngaygiulhd,ghichuct,n_thuongkhac,"",0,0,0,0,"");

                    i++;
                    this.Text = "Lý lịch nhân viên: " + i.ToString() + " dòng";
                    this.Update();

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }

            return gttv;
        }
        private void Lylichnv()
        {

            mDB.Execute_data("delete from nhansu.llnv");
            mDB.Execute_data("delete from nhansu.hopdong");
            DataSet ds = new DataSet();
            DataSet ds1 = new DataSet();
            ds = get_data_fox("select iif(cmndngay={},'','1') as ngay_capcmnd,a.*,'' as dtdd,'99' as macd,year(ngaysinh) as namsinh,ngaysinh as m_ngaysinh from llnv a where ! delete() order by manv");
            dataGridView1.DataSource = ds.Tables[0]; 
            bool gttv;
            int i = 0;

            foreach (DataRow r in ds.Tables[0].Rows)
            {

                try
                {
                    gttv = mDB.Insert_lylichnv(r["manv"].ToString(), r["manv"].ToString(), r["madv"].ToString(), r["stt"].ToString(), c_v.abc_uni(r["ho"].ToString().Trim()),
                        c_v.abc_uni(r["ten"].ToString().Trim()), c_v.abc_uni(r["bidanh"].ToString().Trim()), (r["m_ngaysinh"].ToString() != "" ? mDB.StringToMMDDYYYY(r["ngaysinh"].ToString().Substring(0, 10)) : ""), c_v.abc_uni(r["phai"].ToString()),
                        c_v.abc_uni(r["noisinh"].ToString().Trim()), c_v.abc_uni(r["quequan"].ToString()),
                        c_v.abc_uni(r["diachi"].ToString()), r["t_diachi"].ToString(), r["h_diachi"].ToString(),
                        c_v.abc_uni(r["noio"].ToString().Trim()), r["t_noio"].ToString(), r["h_noio"].ToString().Trim(), r["dienthoai"].ToString(),
                        r["cmndso"].ToString(), (r["ngay_capcmnd"].ToString()== "1" ? mDB.StringToMMDDYYYY(r["cmndngay"].ToString().Substring(0, 10)) : ""), c_v.abc_uni(r["noicapcmnd"].ToString()), "25",
                        r["matgiao"].ToString(), "01", "", int.Parse(r["namsinh"].ToString()),
                        r["dtdd"].ToString(), r["macd"].ToString(), "0", "0", "0","","","");
                    if (gttv)
                    {
                        ds1 = get_data_fox("select a.*,'' as m_ngaybn  from hopdong a where manv='" + r["manv"].ToString() + "'");
                        foreach (DataRow r1 in ds1.Tables[0].Rows)
                        {

                            mDB.Insert_hopdongLylich(r["manv"].ToString(), r["madv"].ToString(), r["manv"].ToString(), r["stt"].ToString().PadLeft(5, '0'),
                                (r1["macv"].ToString() != "" ? r1["macv"].ToString() : "999"), c_v.abc_uni(r1["c_thich_cv"].ToString().Trim()), "",0,decimal.Parse(r1["hs_cv"].ToString()));
                            mDB.Execute_data("update " + mDB.user + ".hopdong set lan=1 where manv='" + r["manv"].ToString() + "'");

                        }
                        i++;
                        this.Text = "Lý lịch nhân viên: " + i.ToString() + " dòng";
                        this.Update();
                    }

                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.ToString());
                }
            }
            this.Text = "Lý lịch nhân viên: Xong";
            this.Update();
        }
        private void Ctdaotao()
        {
            mDB.Execute_data("delete from nhansu.ctdaotao");
            string sql = "";
            sql = "select * from ctdaotao where !delete()";
            DataSet ds = new DataSet();
            ds = get_data_fox(sql);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Insert_CTDAOTAO(r["mactdt"].ToString(),c_v.abc_uni(r["tenctdt"].ToString()), "01");
                i++;
                this.Text = "Chuyển Ngành đt: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            this.Text = "Chuyển đã xong ";
            this.Update();

        }
        private void dm_ngach()
        {
            DataSet ds = new DataSet();
            mDB.Execute_data("delete from nhansu.mangach");
            ds = get_data_fox("select * from mangach where !delete() and !empty(ma) order by ma  ");//order by stt
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Insert_DMNGACHCONGCHUC(r["ma"].ToString(), c_v.abc_uni(r["diengiai"].ToString()), (r["b1"].ToString() != "" ? decimal.Parse(r["b1"].ToString()) : 0),
                                           (r["b2"].ToString() != "" ? decimal.Parse(r["b2"].ToString()) : 0), (r["b3"].ToString() != "" ? decimal.Parse(r["b3"].ToString()) : 0),
                                           (r["b4"].ToString() != "" ? decimal.Parse(r["b4"].ToString()) : 0), (r["b5"].ToString() != "" ? decimal.Parse(r["b5"].ToString()) : 0),
                                           (r["b6"].ToString() != "" ? decimal.Parse(r["b6"].ToString()) : 0), (r["b7"].ToString() != "" ? decimal.Parse(r["b7"].ToString()) : 0),
                                           (r["b8"].ToString() != "" ? decimal.Parse(r["b8"].ToString()) : 0), (r["b9"].ToString() != "" ? decimal.Parse(r["b9"].ToString()) : 0),
                                           (r["b10"].ToString() != "" ? decimal.Parse(r["b10"].ToString()) : 0), (r["b11"].ToString() != "" ? decimal.Parse(r["b11"].ToString()) : 0),
                                           (r["b12"].ToString() != "" ? decimal.Parse(r["b12"].ToString()) : 0), "", r["loaingach"].ToString(), r["nhomngach"].ToString(), (r["stt"].ToString() != "" ? decimal.Parse(r["stt"].ToString()) : 0));
                mDB.Execute_data("update nhansu.mangach set stt=" + r["stt"].ToString() + " where ma='" + r["ma"].ToString() + "'");
                i++;
                this.Text = "Chuyển Ngạch công chức: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            this.Text = "Chuyển đã xong ";
            this.Update();
        }
        private void Bancap()
        {
            mDB.Execute_data("delete from nhansu.bangcap");
            string sql = "";
            sql = "select * from bangcap where !delete()";
            
            DataSet ds = new DataSet();
            ds = get_data_fox(sql);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.upd_DMBANGCAP(r["mabangcap"].ToString(), c_v.abc_uni(r["tenbang"].ToString()), r["mabc"].ToString(),Int32.Parse( r["stt"].ToString()));
                i++;
                this.Text = "Chuyển bằng cấp: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            this.Text = "Chuyển đã xong ";
            this.Update();

        }
        private void dm_hocvan()
        {
            DataSet ds = new DataSet();
            ds = get_data_fox("select * from hocvan where delete()=.f.");//order by stt
            int rec = 0;
            rec = ds.Tables[0].Rows.Count;
            mDB.Execute_data("Delete from nhansu.hocvan");
            int i = 0;
            foreach (DataRow r in ds.Tables[0].Rows)
            {

                mDB.upd_DMHOCVAN(r["mahv"].ToString(),c_v.abc_uni( r["tenhv"].ToString()));
                i++;
                this.Text = "Chuyển học vấn: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            this.Text = "Chuyển đã xong ";
            this.Update();
        }
        private void dm_cvu()
        {
            DataSet ds = new DataSet();
            ds = get_data_fox("select * from macv");//order by stt
            int rec = 0;
            rec = ds.Tables[0].Rows.Count;
            mDB.Execute_data("Delete from nhansu.dmcv");
            int i = 0;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Insert_DMCHUCVU(r["macv"].ToString(), c_v.abc_uni(r["diengiai"].ToString()), r["tenvt"].ToString(),decimal.Parse(r["stt"].ToString()),decimal.Parse(r["heso"].ToString()), Convert.ToBoolean(r["lanhdao"].ToString()));
                i++;
                this.Text = "Chuyển chức vụ: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            this.Text = "Chuyển chức vụ đã xong ";
            this.Update();
        }

        private void Opendata_Click(object sender, EventArgs e)
        {
            try
            {
                string atmp = System.Environment.CurrentDirectory, atmp1 = "";
                SaveFileDialog asf = new SaveFileDialog();
                asf.CheckPathExists = true;
                asf.InitialDirectory = txtPath.Text;
                asf.FileName = "tmp";
                if (asf.ShowDialog() == DialogResult.OK)
                {
                    if (asf.FileName != "")
                    {
                        txtPath.Text = asf.FileName;
                        try
                        {
                            atmp1 = txtPath.Text.Substring(0, txtPath.Text.LastIndexOf("\\") + 1);
                        }
                        catch
                        {
                            atmp1 = "";
                        }
                        txtPath.Text = atmp1;
                    }
                }
                asf.Dispose();
                System.Environment.CurrentDirectory = atmp;
                SaveSettings();
                //----------------------------//
                
            }
            catch
            {
            }
        }

        private void cmdCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void SaveSettings()
        {
            //Settings.Default.data_fox = txtPath.Text;
            //Settings.Default.Save();
        }
    }
}